package com.css.bjdt_core.reportExamine.service;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.nutz.dao.Cnd;
import org.nutz.dao.Condition;
import org.nutz.dao.Dao;
import org.nutz.dao.Sqls;
import org.nutz.dao.entity.Record;
import org.nutz.dao.sql.Sql;
import org.nutz.dao.sql.SqlCallback;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import com.css.bjdt_core.cell.entity.CellHistoryData;
import com.css.bjdt_core.cell.entity.VarParam;
import com.css.bjdt_core.dnDzz.service.DnDzzService;
import com.css.bjdt_core.importExport.entity.RptBatchTemplateRelIE.ExamineState;
import com.css.bjdt_core.reportExamine.entity.RptCellExamineInfo;
import com.css.bjdt_core.reportFill.entity.ViewUnitTemplate;
import com.css.bjdt_core.reportPrepare.entity.RptUnitAnnualRel;
import com.css.bjdt_core.reportPrepare.service.RptUnitService;
import com.css.bjdt_core.util.PageQuerys;
import com.css.bjdt_core.util.ReportConstant;
import com.css.bjdt_core.validation.service.ValidationInfoService;

@Service
public class ReportExamineService {
	@Autowired
	private Dao dao;
	@Autowired
	private ValidationInfoService validationInfoSvc;
	@Autowired
	private RptUnitService rptUnitService;
	@Autowired
	private DnDzzService dnDzzService;

	/**
	 * 分页查询所有需要审核的报表
	 * @param cnd
	 * @return
	 */
	public Map queryReportExaminePage(Condition cnd, int pageNum, int pageSize) {
		Map result = PageQuerys.ligerGridQuery(dao, ViewUnitTemplate.class, cnd, pageNum, pageSize);
		return result;
	}

	//判断是否有当前年度的审批表
	public void isExistExamineTable(String annualId) {
		String tableName = ReportConstant.EXAMINE_TABLE_PREFIX + annualId;
		String count = validationInfoSvc.executeSql(ReportConstant.ISEXIST_TABLE_SQL, Sqls.callback.str(),
				new VarParam(null, null, "table_name", tableName)).getString();
		//Mysql没有表返回null oracle返回"0"
		if (count == null || "0".equals(count)) {
			//if ("0".equals(count)) {
			validationInfoSvc.executeSql(ReportConstant.EXAMINE_CREATE_TABLE_SQL, null,
					new VarParam("table_name", tableName, null, null));
		}
	}

	//判断是否有当前年度的审批历史记录表
	public void isExistExamineHistoryTable(String annualId) {
		String tableName = ReportConstant.EXAMINE_HISTORY_TABLE_PREFIX + annualId;
		String count = validationInfoSvc.executeSql(ReportConstant.ISEXIST_TABLE_SQL, Sqls.callback.str(),
				new VarParam(null, null, "table_name", tableName)).getString();
		//Mysql没有表返回null oracle返回"0"
		if (count == null || "0".equals(count)) {
			//if ("0".equals(count)) {
			validationInfoSvc.executeSql(ReportConstant.EXAMINE_HISTORY_CREATE_TABLE_SQL, null,
					new VarParam("table_name", tableName, null, null));
		}
	}

	//插入报表审批内容
	public void saveCellExamineInfo(RptCellExamineInfo rptCellExamineInfo, String annualId) {
		String tableName = ReportConstant.EXAMINE_TABLE_PREFIX + annualId;
		String strSql = ReportConstant.EXAMINE_INSERT_SQL.replace("$table_name", tableName);
		Sql sql = Sqls.create(strSql);
		sql.params().set("ID", rptCellExamineInfo.getId());
		sql.params().set("CELL_ID", rptCellExamineInfo.getCellId());
		sql.params().set("TEMPLATE_ID", rptCellExamineInfo.getTemplateId());
		sql.params().set("EXAMINE_INFO", rptCellExamineInfo.getExamineInfo());
		sql.params().set("EXAMINE_DZZID", rptCellExamineInfo.getExamineDzzId());
		sql.params().set("EXAMINE_TIME", rptCellExamineInfo.getExamineTime());
		sql.params().set("ABS_ROW_NUM", rptCellExamineInfo.getAbsRowNum());
		sql.params().set("ABS_COL_NUM", rptCellExamineInfo.getAbsColNum());
		sql.params().set("REL_ROW_NUM", rptCellExamineInfo.getRelRowNum());
		sql.params().set("REL_COL_NUM", rptCellExamineInfo.getRelColNum());
		sql.params().set("BE_AUDITED_DZZID", rptCellExamineInfo.getBeAuditeddzzId());
		sql.params().set("REPLY_TIME", rptCellExamineInfo.getReplyTime());
		sql.params().set("ERROR_TYPE", rptCellExamineInfo.getErrorType());
		sql.params().set("REPLY_STATE", rptCellExamineInfo.getReplyState());
		dao.execute(sql);
	}

	//插入报表审批内容
	public void updateCellAExamineInfo(String examineInfo, String examineTime, String examineId, String annualId) {
		String tableName = ReportConstant.EXAMINE_TABLE_PREFIX + annualId;
		String strSql = "update $table_name set EXAMINE_INFO = @examineInfo, EXAMINE_TIME = @examineTime where ID = @examineId"
				.replace("$table_name", tableName);
		Sql sql = Sqls.create(strSql);
		sql.params().set("examineInfo", examineInfo);
		sql.params().set("examineTime", examineTime);
		sql.params().set("examineId", examineId);
		dao.execute(sql);
	}

	//填写报表回复内容
	public void saveCellAExamineInfoReply(RptCellExamineInfo rptCellExamineInfo, String annualId) {
		String tableName = ReportConstant.EXAMINE_TABLE_PREFIX + annualId;
		String strSql = "update $table_name set BE_AUDITED_DZZID = @beAuditedDzzId, REPLY_TIME = @replyTime, REPLY_INFO = @replyInfo, ERROR_TYPE = @errorType,REPLY_STATE = @replyState where ID = @examineId"
				.replace("$table_name", tableName);
		Sql sql = Sqls.create(strSql);
		sql.params().set("beAuditedDzzId", rptCellExamineInfo.getBeAuditeddzzId());
		sql.params().set("replyTime", rptCellExamineInfo.getReplyTime());
		sql.params().set("replyInfo", rptCellExamineInfo.getReplyInfo());
		sql.params().set("errorType", rptCellExamineInfo.getErrorType());
		sql.params().set("replyState", rptCellExamineInfo.getReplyState());
		sql.params().set("examineId", rptCellExamineInfo.getId());
		dao.execute(sql);
	}

	//根据ID得到一条审批记录
	public RptCellExamineInfo getCellAExamineInfo(String examineId, String annualId) {
		String tableName = ReportConstant.EXAMINE_TABLE_PREFIX + annualId;
		String strSql = "select ID,EXAMINE_INFO,REPLY_INFO,ERROR_TYPE, REPLY_STATE from $table_name where ID = @examineId"
				.replace("$table_name", tableName);
		Sql sql = Sqls.create(strSql);
		sql.params().set("examineId", examineId);
		sql.setCallback(new SqlCallback() {
			public Object invoke(Connection conn, ResultSet rs, Sql sql) throws SQLException {
				RptCellExamineInfo rptCellExamineInfo = new RptCellExamineInfo();
				while (rs.next()) {
					rptCellExamineInfo.setId(rs.getString("ID"));
					rptCellExamineInfo.setExamineInfo(rs.getString("EXAMINE_INFO"));
					rptCellExamineInfo.setReplyInfo(rs.getString("REPLY_INFO"));
					rptCellExamineInfo.setErrorType(rs.getString("ERROR_TYPE"));
					rptCellExamineInfo.setReplyState(rs.getString("REPLY_STATE"));
				}
				return rptCellExamineInfo;
			}
		});
		dao.execute(sql);
		return sql.getObject(RptCellExamineInfo.class);
	}

	public List<CellHistoryData> queryCellExamineHistroy(String annualId, String templateId, String dzzId) {
		String tableName = ReportConstant.EXAMINE_HISTORY_TABLE_PREFIX + annualId;
		String strSql = ("select ABS_ROW_NUM,ABS_COL_NUM,EXAMINE_TIME,CELL_VALUE,HISTORY_RYCELL_VALUE,DZZ_ID,TB_DZZ_ID,REL_ROW_NUM,REL_COL_NUM from "
				+ tableName + " where TEMPLATE_ID = @templateId and DZZ_ID = @dzzId ");
		Sql sql = Sqls.create(strSql);
		sql.params().set("templateId", templateId).set("dzzId", dzzId);
		//根据SQL先查出来该张报表的所有审核信息
		sql.setCallback(new SqlCallback() {

			public Object invoke(Connection conn, ResultSet rs, Sql sql) throws SQLException {
				List<CellHistoryData> rptCellExamineInfoList = new ArrayList<CellHistoryData>();
				while (rs.next()) {
					CellHistoryData rptCellExamineInfo = new CellHistoryData();
					rptCellExamineInfo.setAbsColNum(rs.getString("ABS_COL_NUM"));
					rptCellExamineInfo.setAbsRowNum(rs.getString("ABS_ROW_NUM"));
					rptCellExamineInfo.setStartTime(rs.getString("EXAMINE_TIME"));
					rptCellExamineInfo.setCellValue(rs.getString("CELL_VALUE"));
					rptCellExamineInfo.setHistoryCellValue(rs.getString("HISTORY_RYCELL_VALUE"));
					rptCellExamineInfo.setDzzId(rs.getString("DZZ_ID"));
					rptCellExamineInfo.setTbDzzId(rs.getString("TB_DZZ_ID"));
					rptCellExamineInfo.setRelColNum(rs.getString("REL_COL_NUM"));
					rptCellExamineInfo.setRelRowNum(rs.getString("REL_ROW_NUM"));
					rptCellExamineInfoList.add(rptCellExamineInfo);
				}
				return rptCellExamineInfoList;
			}
		});
		dao.execute(sql);
		return sql.getList(CellHistoryData.class);
	}

	//插入报表审批内容历史记录
	public void saveCellExamineHistroy(CellHistoryData cellHistoryData, String annualId) {
		String tableName = ReportConstant.EXAMINE_HISTORY_TABLE_PREFIX + annualId;
		String strSql = ReportConstant.EXAMINE_HISTORY_INSERT_SQL.replace("$table_name", tableName);
		Sql sql = Sqls.create(strSql);
		sql.params().set("ID", cellHistoryData.getId());
		sql.params().set("CELL_ID", cellHistoryData.getCellId());
		sql.params().set("CELL_DATA_ID", cellHistoryData.getCellId());
		sql.params().set("CELL_VALUE", cellHistoryData.getCellValue());
		sql.params().set("HISTORY_RYCELL_VALUE", cellHistoryData.getHistoryCellValue());
		sql.params().set("EXAMINE_TIME", cellHistoryData.getTime());
		sql.params().set("ABS_ROW_NUM", cellHistoryData.getAbsRowNum());
		sql.params().set("ABS_COL_NUM", cellHistoryData.getAbsColNum());
		sql.params().set("REL_ROW_NUM", cellHistoryData.getRelRowNum());
		sql.params().set("REL_COL_NUM", cellHistoryData.getRelColNum());
		sql.params().set("DZZ_ID", cellHistoryData.getDzzId());
		sql.params().set("TEMPLATE_ID", cellHistoryData.getTemplateId());
		sql.params().set("TB_DZZ_ID", cellHistoryData.getTbDzzId());
		dao.execute(sql);
	}

	//根据报表ID和党组织ID
	public List<RptCellExamineInfo> getAllExamineInfoByTemplateDataId(String templateId, String annualId,
			String dzzId) {
		String tableName = ReportConstant.EXAMINE_TABLE_PREFIX + annualId;
		//	String strSql = ("select ABS_ROW_NUM,ABS_COL_NUM,ERROR_TYPE,EXAMINE_TIME,EXAMINE_INFO from " + tableName
		//			+ " where TEMPLATE_ID = @templateId and DZZ_ID = @dzzId " + "group by ABS_ROW_NUM,ABS_COL_NUM");
		String strSql = ("select ABS_ROW_NUM,ABS_COL_NUM  from " + tableName
				+ " where TEMPLATE_ID = @templateId and BE_AUDITED_DZZID = @dzzId  and REPLY_STATE in('1','0')"
				+ " group by ABS_ROW_NUM,ABS_COL_NUM");
		Sql sql = Sqls.create(strSql);
		sql.params().set("templateId", templateId).set("dzzId", dzzId);
		//根据SQL先查出来该张报表的所有审核信息
		sql.setCallback(new SqlCallback() {

			public Object invoke(Connection conn, ResultSet rs, Sql sql) throws SQLException {
				List<RptCellExamineInfo> rptCellExamineInfoList = new ArrayList<RptCellExamineInfo>();
				while (rs.next()) {
					RptCellExamineInfo rptCellExamineInfo = new RptCellExamineInfo();
					rptCellExamineInfo.setAbsColNum(rs.getString("ABS_COL_NUM"));
					rptCellExamineInfo.setAbsRowNum(rs.getString("ABS_ROW_NUM"));
					/*rptCellExamineInfo.setRelRowNum(rs.getString("REL_ROW_NUM"));
					rptCellExamineInfo.setRelColNum(rs.getString("REL_COL_NUM"));
					rptCellExamineInfo.setExamineInfo(rs.getString("EXAMINE_INFO"));
					rptCellExamineInfo.setExamineTime(rs.getString("EXAMINE_TIME"));
					rptCellExamineInfo.setReplyTime(rs.getString("REPLY_TIME"));
					rptCellExamineInfo.setReplyInfo(rs.getString("REPLY_INFO"));
					rptCellExamineInfo.setErrorType(rs.getString("ERROR_TYPE"));*/

					rptCellExamineInfoList.add(rptCellExamineInfo);
				}
				return rptCellExamineInfoList;
			}
		});
		dao.execute(sql);
		return sql.getList(RptCellExamineInfo.class);
	}

	//根据报表ID和党组织ID
	public List<RptCellExamineInfo> getAllExamineInfo(String templateId, String annualId, String dzzId) {
		String tableName = ReportConstant.EXAMINE_TABLE_PREFIX + annualId;
		String strSql = ("select ABS_ROW_NUM,ABS_COL_NUM,ERROR_TYPE,EXAMINE_TIME,EXAMINE_INFO from " + tableName
				+ " where TEMPLATE_ID = @templateId and DZZ_ID = @dzzId and  ABS_ROW_NUM is not null and ABS_COL_NUM is not null ");
		Sql sql = Sqls.create(strSql);
		sql.params().set("templateId", templateId).set("dzzId", dzzId);
		//根据SQL先查出来该张报表的所有审核信息
		sql.setCallback(new SqlCallback() {

			public Object invoke(Connection conn, ResultSet rs, Sql sql) throws SQLException {
				List<RptCellExamineInfo> rptCellExamineInfoList = new ArrayList<RptCellExamineInfo>();
				while (rs.next()) {
					RptCellExamineInfo rptCellExamineInfo = new RptCellExamineInfo();
					rptCellExamineInfo.setAbsColNum(rs.getString("ABS_COL_NUM"));
					rptCellExamineInfo.setAbsRowNum(rs.getString("ABS_ROW_NUM"));
					rptCellExamineInfo.setErrorType(rs.getString("ERROR_TYPE"));
					rptCellExamineInfo.setExamineTime(rs.getString("EXAMINE_TIME"));
					rptCellExamineInfo.setExamineInfo(rs.getString("EXAMINE_INFO"));
					rptCellExamineInfoList.add(rptCellExamineInfo);
				}
				return rptCellExamineInfoList;
			}
		});
		dao.execute(sql);
		return sql.getList(RptCellExamineInfo.class);
	}

	//根据报表ID和党组织ID 返回的是相对坐标 用于导出excel
	public List<RptCellExamineInfo> getAllExamineInfoRel(String templateId, String annualId, String dzzId) {
		String tableName = ReportConstant.EXAMINE_TABLE_PREFIX + annualId;
		String strSql = "select ID,TEMPLATE_ID,EXAMINE_INFO,EXAMINE_DZZID,EXAMINE_TIME,ABS_ROW_NUM,ABS_COL_NUM,REL_ROW_NUM,REL_COL_NUM,BE_AUDITED_DZZID,REPLY_TIME,REPLY_INFO,ERROR_TYPE, REPLY_STATE from "
				+ tableName + " t " + " where t.TEMPLATE_ID = @templateId and t.BE_AUDITED_DZZID = @dzzId ";
		Sql sql = Sqls.create(strSql);
		sql.params().set("templateId", templateId).set("dzzId", dzzId);
		//根据SQL先查出来该张报表的所有审核信息
		sql.setCallback(new SqlCallback() {

			public Object invoke(Connection conn, ResultSet rs, Sql sql) throws SQLException {
				List<RptCellExamineInfo> rptCellExamineInfoList = new ArrayList<RptCellExamineInfo>();
				while (rs.next()) {
					RptCellExamineInfo rptCellExamineInfo = new RptCellExamineInfo();
					rptCellExamineInfo.setId(rs.getString("ID"));
					rptCellExamineInfo.setTemplateId(rs.getString("TEMPLATE_ID"));
					rptCellExamineInfo.setExamineInfo(rs.getString("EXAMINE_INFO"));
					rptCellExamineInfo.setExamineDzzId(rs.getString("EXAMINE_DZZID"));
					rptCellExamineInfo.setExamineTime(rs.getString("EXAMINE_TIME"));
					rptCellExamineInfo.setAbsColNum(rs.getString("ABS_COL_NUM"));
					rptCellExamineInfo.setAbsRowNum(rs.getString("ABS_ROW_NUM"));
					rptCellExamineInfo.setRelRowNum(rs.getString("REL_ROW_NUM"));
					rptCellExamineInfo.setRelColNum(rs.getString("REL_COL_NUM"));
					rptCellExamineInfo.setBeAuditeddzzId(rs.getString("BE_AUDITED_DZZID"));
					rptCellExamineInfo.setReplyTime(rs.getString("REPLY_TIME"));
					rptCellExamineInfo.setReplyInfo(rs.getString("REPLY_INFO"));
					rptCellExamineInfo.setErrorType(rs.getString("ERROR_TYPE"));
					rptCellExamineInfo.setReplyState(rs.getString("REPLY_STATE"));
					rptCellExamineInfoList.add(rptCellExamineInfo);
				}
				return rptCellExamineInfoList;
			}
		});
		dao.execute(sql);
		return sql.getList(RptCellExamineInfo.class);
	}

	public List<RptCellExamineInfo> getAllExamineInfoByABS(String templateId, String annualId, String col, String row,
			String dzzId) {
		String tableName = ReportConstant.EXAMINE_TABLE_PREFIX + annualId;
		String strSql = ("select ABS_ROW_NUM,ABS_COL_NUM,ERROR_TYPE from " + tableName
				+ " where TEMPLATE_ID = @templateId and BE_AUDITED_DZZID=@dzzId and ABS_ROW_NUM=@row and ABS_COL_NUM=@col order by EXAMINE_TIME desc ");
		Sql sql = Sqls.create(strSql);
		sql.params().set("templateId", templateId);
		sql.params().set("dzzId", dzzId);
		sql.params().set("col", col);
		sql.params().set("row", row);
		//根据SQL先查出来该张报表的所有审核信息
		sql.setCallback(new SqlCallback() {
			public Object invoke(Connection conn, ResultSet rs, Sql sql) throws SQLException {
				List<RptCellExamineInfo> rptCellExamineInfoList = new ArrayList<RptCellExamineInfo>();
				while (rs.next()) {
					RptCellExamineInfo rptCellExamineInfo = new RptCellExamineInfo();
					rptCellExamineInfo.setAbsColNum(rs.getString("ABS_COL_NUM"));
					rptCellExamineInfo.setAbsRowNum(rs.getString("ABS_ROW_NUM"));
					rptCellExamineInfo.setErrorType(rs.getString("ERROR_TYPE"));
					rptCellExamineInfoList.add(rptCellExamineInfo);
				}
				return rptCellExamineInfoList;
			}
		});
		dao.execute(sql);
		return sql.getList(RptCellExamineInfo.class);
	}

	//根据报表实例ID、年度ID、行号列号
	public List<RptCellExamineInfo> getACellExamineInfo(String templateId, String annualId, String absColNum,
			String absRowNum, String dzzId) {
		String tableName = ReportConstant.EXAMINE_TABLE_PREFIX + annualId;
		String strSql = ReportConstant.EXAMINE_QUERY_CELL_SQL.replace("$table_name", tableName);
		Sql sql = Sqls.create(strSql);
		sql.params().set("templateId", templateId).set("absRowNum", absRowNum).set("absColNum", absColNum)
				.set("examineDzzId", dzzId);
		//根据SQL先查出来该张报表的所有审核信息
		sql.setCallback(new SqlCallback() {

			public Object invoke(Connection conn, ResultSet rs, Sql sql) throws SQLException {
				List<RptCellExamineInfo> rptCellExamineInfoList = new ArrayList<RptCellExamineInfo>();
				while (rs.next()) {
					RptCellExamineInfo rptCellExamineInfo = new RptCellExamineInfo();
					rptCellExamineInfo.setExamineInfo(rs.getString("EXAMINE_INFO"));
					rptCellExamineInfo.setAbsColNum(rs.getString("ABS_COL_NUM"));
					rptCellExamineInfo.setAbsRowNum(rs.getString("ABS_ROW_NUM"));
					rptCellExamineInfo.setExamineDzzId(rs.getString("EXAMINE_DZZID"));
					rptCellExamineInfo.setErrorType(rs.getString("ERROR_TYPE"));
					rptCellExamineInfo.setExamineTime(rs.getString("EXAMINE_TIME"));
					rptCellExamineInfo.setBeAuditeddzzId(rs.getString("BE_AUDITED_DZZID"));
					rptCellExamineInfo.setReplyInfo(rs.getString("REPLY_INFO"));
					rptCellExamineInfo.setReplyTime(rs.getString("REPLY_TIME"));
					rptCellExamineInfo.setId(rs.getString("ID"));
					rptCellExamineInfo.setReplyState(rs.getString("REPLY_STATE"));
					rptCellExamineInfoList.add(rptCellExamineInfo);
				}
				return rptCellExamineInfoList;
			}
		});
		dao.execute(sql);
		//List<RptCellExamineInfo> list = new ArrayList<RptCellExamineInfo>();
		//根据审核单位ID，查出审核单位的名称
		/*for (RptCellExamineInfo rptCellExamineInfo : ) {
			DnDzz dnDzz = dnDzzService.getDnDzzDataByDzzId(rptCellExamineInfo.getTbDzzId());
			rptCellExamineInfo.setExamineUnitName(dnDzz.getDzzmc());
			list.add(rptCellExamineInfo);
		}*/
		return sql.getList(RptCellExamineInfo.class);
	}

	//报表审核通过
	public void examinePassReportSevice(String batchId) {
		String strSql = "update RPT_BATCH_TEMPLATE_R set EXAMINE_STATE = @examineState where BATCH_ID = @batchId";
		Sql sql = Sqls.create(strSql);
		sql.params().set("examineState", ExamineState.examineAhrough.getKey()).set("batchId", batchId);
		dao.execute(sql);
	}

	//报表审核退回
	public void examineReturnReportSevice(String batchId) {
		String strSql = "update RPT_BATCH_TEMPLATE_R set EXAMINE_STATE = @examineState where BATCH_ID = @batchId";
		Sql sql = Sqls.create(strSql);
		sql.params().set("examineState", ExamineState.returnReport.getKey()).set("batchId", batchId);
		dao.execute(sql);
	}

	public RptUnitAnnualRel getARptUnitAnnualRel(String annualId, String dzzId) {
		return dao.fetch(RptUnitAnnualRel.class, Cnd.where("ANNUAL_ID", "=", annualId).and("DZZ_ID", "=", dzzId));
	}

	public RptUnitAnnualRel getARptUnitAnnualRelByUnitId(String annualId, String unitId) {
		return dao.fetch(RptUnitAnnualRel.class, Cnd.where("ANNUAL_ID", "=", annualId).and("UNIT_ID", "=", unitId));
	}

	public void deleteAExamineInfoById(String annualId, String id) {
		String tableName = ReportConstant.EXAMINE_TABLE_PREFIX + annualId;
		String strSql = "delete from " + tableName + " where ID = @id";
		Sql sql = Sqls.create(strSql);
		sql.params().set("id", id);
		dao.execute(sql);
	}

	//根据报表实例ID、年度ID、行号列号
	public List<RptCellExamineInfo> getACellExamineReplyInfo(String templateId, String annualId, String absColNum,
			String absRowNum, String dzzId) {
		String tableName = ReportConstant.EXAMINE_TABLE_PREFIX + annualId;
		String strSql = ReportConstant.EXAMINE_REPLY_QUERY_CELL_SQL.replace("$table_name", tableName);
		Sql sql = Sqls.create(strSql);
		sql.params().set("templateId", templateId).set("absRowNum", absRowNum).set("absColNum", absColNum)
				.set("examineDzzId", dzzId);
		//根据SQL先查出来该张报表的所有审核信息
		sql.setCallback(new SqlCallback() {

			public Object invoke(Connection conn, ResultSet rs, Sql sql) throws SQLException {
				List<RptCellExamineInfo> rptCellExamineInfoList = new ArrayList<RptCellExamineInfo>();
				while (rs.next()) {
					RptCellExamineInfo rptCellExamineInfo = new RptCellExamineInfo();
					rptCellExamineInfo.setExamineInfo(rs.getString("EXAMINE_INFO"));
					rptCellExamineInfo.setAbsColNum(rs.getString("ABS_COL_NUM"));
					rptCellExamineInfo.setAbsRowNum(rs.getString("ABS_ROW_NUM"));
					rptCellExamineInfo.setExamineDzzId(rs.getString("EXAMINE_DZZID"));
					rptCellExamineInfo.setErrorType(rs.getString("ERROR_TYPE"));
					rptCellExamineInfo.setExamineTime(rs.getString("EXAMINE_TIME"));
					rptCellExamineInfo.setBeAuditeddzzId(rs.getString("BE_AUDITED_DZZID"));
					rptCellExamineInfo.setReplyInfo(rs.getString("REPLY_INFO"));
					rptCellExamineInfo.setReplyTime(rs.getString("REPLY_TIME"));
					rptCellExamineInfo.setReplyState(rs.getString("REPLY_STATE"));

					rptCellExamineInfo.setId(rs.getString("ID"));
					rptCellExamineInfoList.add(rptCellExamineInfo);
				}
				return rptCellExamineInfoList;
			}
		});
		dao.execute(sql);
		//List<RptCellExamineInfo> list = new ArrayList<RptCellExamineInfo>();
		//根据审核单位ID，查出审核单位的名称
		/*for (RptCellExamineInfo rptCellExamineInfo : ) {
			DnDzz dnDzz = dnDzzService.getDnDzzDataByDzzId(rptCellExamineInfo.getTbDzzId());
			rptCellExamineInfo.setExamineUnitName(dnDzz.getDzzmc());
			list.add(rptCellExamineInfo);
		}*/
		return sql.getList(RptCellExamineInfo.class);
	}

	//查询当前单位下的所有审核信息分页方法，针对Layui的分页使用的
	public Map queryAllExamineInfoPagedByDzzId(String beAuditedDzzId, String annualId, int pageNum, int pageSize,
			String sortname, String sortorder) {
		String tableName = ReportConstant.EXAMINE_TABLE_PREFIX + annualId;

		String selectStr = "select ID,ABS_ROW_NUM,ABS_COL_NUM,REL_ROW_NUM,REL_COL_NUM,EXAMINE_INFO,EXAMINE_TIME,REPLY_TIME,REPLY_INFO,ERROR_TYPE,TEMPLATE_ID,t1.NAME as template_name ";
		String countStr = "select count(1) ";
		String fromStr = "from " + tableName
				+ " t left join RPT_TEMPLATE t1 on t.TEMPLATE_ID = t1.TEMPLATEID where BE_AUDITED_DZZID = @beAuditedDzzId";
		String fromCountStr = "from " + tableName + " where BE_AUDITED_DZZID = @beAuditedDzzId";

		String orderStr = "order by " + sortname + " " + sortorder;

		Sql sql = null;
		if (sortname == null || sortname.equals("")) {
			sql = Sqls.queryRecord(selectStr + fromStr);
		} else {
			sql = Sqls.queryRecord(selectStr + fromStr + " " + orderStr);
		}

		sql.params().set("beAuditedDzzId", beAuditedDzzId);

		Sql sqlCount = Sqls.fetchInt(countStr + fromCountStr);
		sqlCount.params().set("beAuditedDzzId", beAuditedDzzId);

		sql.setPager(
				dao.createPager((pageNum < 1) ? 1 : pageNum, (pageSize < 1) ? PageQuerys.DEFAULT_PAGE_SIZE : pageSize));
		dao.execute(sqlCount, sql);
		Map<String, Object> result = new HashMap<String, Object>();
		result.put("Rows", sql.getList(Record.class));
		result.put("Total", sqlCount.getInt());
		return result;
	}

	//查询当前单位下的所有审核信息分页方法，针对Layui的分页使用的
	public Map queryAllExamineInfoPagedByDzzIdAndTempId(String beAuditedDzzId, String annualId, String tempId,
			int pageNum, int pageSize, String sortname, String sortorder) {
		String tableName = ReportConstant.EXAMINE_TABLE_PREFIX + annualId;

		String selectStr = "select ID,ABS_ROW_NUM,ABS_COL_NUM,REL_ROW_NUM,REL_COL_NUM,EXAMINE_INFO,EXAMINE_TIME,REPLY_TIME,REPLY_INFO,ERROR_TYPE,TEMPLATE_ID,t1.NAME as template_name ";
		String countStr = "select count(1) ";
		String fromStr = "from " + tableName
				+ " t left join RPT_TEMPLATE t1 on t.TEMPLATE_ID = t1.TEMPLATEID where BE_AUDITED_DZZID = @beAuditedDzzId and TEMPLATE_ID = @tempId";
		String fromCountStr = "from " + tableName
				+ " where BE_AUDITED_DZZID = @beAuditedDzzId and TEMPLATE_ID = @tempId";

		String orderStr = "order by " + sortname + " " + sortorder;

		Sql sql = null;
		if (sortname == null || sortname.equals("")) {
			sql = Sqls.queryRecord(selectStr + fromStr);
		} else {
			sql = Sqls.queryRecord(selectStr + fromStr + " " + orderStr);
		}

		sql.params().set("beAuditedDzzId", beAuditedDzzId);
		sql.params().set("tempId", tempId);

		Sql sqlCount = Sqls.fetchInt(countStr + fromCountStr);
		sqlCount.params().set("beAuditedDzzId", beAuditedDzzId);
		sql.params().set("tempId", tempId);

		sql.setPager(
				dao.createPager((pageNum < 1) ? 1 : pageNum, (pageSize < 1) ? PageQuerys.DEFAULT_PAGE_SIZE : pageSize));
		dao.execute(sqlCount, sql);
		Map<String, Object> result = new HashMap<String, Object>();
		result.put("Rows", sql.getList(Record.class));
		result.put("Total", sqlCount.getInt());
		return result;
	}

	/**
	 * 更新一个 RepCellExam 對象的 reply_state
	 * @param examineId
	 * @param annualId
	 */
	public void updateReplayStateForObj(String examineId, String annualId) {
		String tableName = ReportConstant.EXAMINE_TABLE_PREFIX + annualId;
		String strSql = "update $table_name set REPLY_STATE = @replayState where ID = @examineId".replace("$table_name",
				tableName);
		Sql sql = Sqls.create(strSql);
		sql.params().set("replayState", "2");
		sql.params().set("examineId", examineId);
		dao.execute(sql);
	}

	/**
	 * 根據annualID和党组织ID,獲得是否全回復，若全回復，則 return 0， else  return >0
	 * @param annualId
	 * @param dzzId
	 * @return
	 * @author lyj
	 */
	public int getNOReplyExamineInfoCount(String annualId, String dzzId) {
		String tableName = ReportConstant.EXAMINE_TABLE_PREFIX + annualId;
		/*String strSql = ("select count(1)  from " + tableName
				+ " where  BE_AUDITED_DZZID = @dzzId  and REPLY_STATE ='0'");
		Sql sql = Sqls.create(strSql);
		sql.params().set("dzzId", dzzId);*/
		return dao.count(tableName, Cnd.where("BE_AUDITED_DZZID", "=", dzzId).and("REPLY_STATE", "=", "0"));

	}

}
